package me.codegenerator2.service.impl;

import me.codegenerator2.exception.BadRequestException;
import me.codegenerator2.service.GeneratorMysqlFromExcelService;
import me.codegenerator2.utils.CommonStringHandleUtil;
import me.codegenerator2.utils.excel.ParseExcelDataUtil;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

/**
 * @author zhongfs
 */
@Service
public class GeneratorMysqlFromExcelServiceImpl implements GeneratorMysqlFromExcelService {

    @Override
    public String generatorMysqlFromExcel(MultipartFile file, String tableName) throws Exception {
        List<String> idNameList = new ArrayList<>(Arrays.asList("ID", "Id", "iD", "id"));
        String fileName = file.getOriginalFilename();
        int at = fileName.lastIndexOf(".");
        if (StringUtils.isBlank(tableName)) {
            String caselsh = fileName.substring(0, at);
            tableName = caselsh;
        }
        StringBuffer sb = new StringBuffer();
        sb.append("INSERT    INTO   ");
        sb.append(tableName);
        sb.append("  (  ");
        InputStream excelInputStream = file.getInputStream();
        Workbook wb = WorkbookFactory.create(excelInputStream);
        String[] parseTableHead = ParseExcelDataUtil.getExcelHead(wb);
        String tableColumnName = CommonStringHandleUtil.stringArrayToStringWithCommaForSql(parseTableHead);
        sb.append(tableColumnName);
        sb.append("  )   VALUES   ");
        List<Map<String, Object>> parseData = ParseExcelDataUtil.parseExcelFile(wb, parseTableHead);
        // for(Map<String, Object> map:parseData) {
        for (int start = 0; start < parseData.size(); start++) {
            Map<String, Object> map = parseData.get(start);
            StringBuffer valueBuffer = new StringBuffer();
            valueBuffer.append("(");
            for (int i = 0; i < parseTableHead.length; i++) {
                String keyName = parseTableHead[i];
                String tmp = (String) map.get(keyName);
                if (StringUtils.isBlank(tmp) || idNameList.contains(keyName)) {
                    valueBuffer.append("null");
                } else {
                    valueBuffer.append("'");
                    valueBuffer.append(tmp);
                    valueBuffer.append("'");

                }

                if (i != parseTableHead.length - 1) {
                    valueBuffer.append(",");
                }
            }
            valueBuffer.append(")");
            if (start != parseData.size() - 1) {
                valueBuffer.append(",");
            }
            sb.append(valueBuffer.toString());
        }
        sb.append(";\n commit;");

        return sb.toString();

    }

    @Override
    public byte[] generatorMysqlFromExcelZip(String comments, String fileName) {
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        ZipOutputStream zip = new ZipOutputStream(outputStream);
        try {
            // 添加到zip
            zip.putNextEntry(new ZipEntry(fileName));
            IOUtils.write(comments, zip, "UTF-8");
            IOUtils.closeQuietly();
            zip.closeEntry();
        } catch (IOException e) {
            throw new BadRequestException("生成压缩文件失败");
        }
        IOUtils.closeQuietly(zip);
        return outputStream.toByteArray();
    }

}
